<?php
/**
 * TextDatabase class: a hackish replacement for SQLite for wtorrent
 *
 * 1. Include this file in '.\lib\inc\includes.inc.php'
 * >  require_once( 'lib/inc/textdb.inc.php' );
 * 2. Amend "new SQLiteDatabase(DB_FILE)" to "new TextDatabase(DB_FILE)"
   >  '.\install.php' - line #29
   >  '.\lib\cls\Web.cls.php' - line #47
 * 3. Install wtorrent using ./install.php as normal
 *
 * @package    wtorrent
 * @subpackage text-database
 * @author     Neil Popham <neilpopham@gmail.com>
 * @version    1.1.3
 * @filesource
 */

/**
 * Text database
 */
class TextDatabase
{
    public $base;
    public $tables;
    public $lastRowID;
    public $numChanged;
    public $lastError;

    /**
     * Constructor
     * @param string $strBase Base name for all table files
     */
    public function __construct($strBase)
    {
        $this->base = DIR_EXEC . (substr(DIR_EXEC, -1) == '/' ? '' : '/') . $strBase;
    }

    /**
     * Destructor
     */
    public function __destruct()
    {

    }

    /**
     * Executes a SQL command
     * @param string $strSQL The SQL to execute
     * @return TextDatabaseResult The result object
     */
    public function query($strSQL)
    {
        // Clear query meta data
        unset($this->lastRowID);
        unset($this->numChanged);
        unset($this->lastError);

        /**
         * CREATE TABLE
         */
        if (preg_match('/^CREATE +TABLE +(\w+) *\((.+)\)/i', $strSQL, $arrParts)) {

            // Extract fields and data types
            if (preg_match_all('/(\w+) +([^,]+),/', $arrParts[2] . ",", $arrFields)) {
                $strTable = $arrParts[1];

                // If the table has not been instantiated create it
                if (!isset($this->tables[$strTable])) {
                    $this->tables[$strTable] = new TextDatabaseTable($strTable, $this->base);
                }

                // Set field names
                $this->tables[$strTable]->fields = $arrFields[1];

                // Wipe any existing data (if table has been created previously)
                $this->tables[$strTable]->truncate();

                // Check to see if we have an integer primary key
                for ($i = 0; $i < count($arrFields[0]); $i++) {
                    if (preg_match('/INTEGER +PRIMARY +KEY/i', $arrFields[2][$i])) {
                        $this->tables[$strTable]->primaryKeyIndex = $i;
                    }
                }

                // Save table to file
                $this->tables[$strTable]->save();

                // Return  TextDatabaseResult
                return new TextDatabaseResult(false, $this->tables[$strTable]->fields);
            }
        }

        /**
         * INSERT INTO
         */
        if (preg_match('/^INSERT +INTO +(\w+) *(\((.+)\) +)*VALUES *\((.+)\)/i', $strSQL, $arrParts)) {
            $strTable = $arrParts[1];

            // If we have a valid list of values
            if (preg_match_all('/(\w|[\'"].*?[\'"])[ ,]/', $arrParts[4] . ",", $arrValues)) {

                // If we have field names
                if (!empty($arrParts[3]) && preg_match_all('/(\w+) *,/', $arrParts[3] . ",", $arrFields)) {
                    if (count($arrFields[1]) != count($arrValues[1])) return false;
                    $arrData = array();
                    for ($i = 0; $i < count($arrValues[1]); $i++) {
                        $arrData[$arrFields[1][$i]] = TextDatabase::tidy($arrValues[1][$i]);
                    }

                // Only values have been passed
                } else {
                    for ($i = 0; $i < count($arrValues[1]); $i++) {
                        $arrData[] = TextDatabase::tidy($arrValues[1][$i]);
                    }
                }

                // If the table has not been instantiated create it
                if (!isset($this->tables[$strTable])) {
                    $this->tables[$strTable] = new TextDatabaseTable($strTable, $this->base);
                }

                // Add the data to the table
                $intRowID = $this->tables[$strTable]->addRow($arrData);
                if (is_integer($intRowID)) $this->lastRowID = $intRowID;
                $this->numChanged = 1;

                // Return TextDatabaseResult
                return new TextDatabaseResult($this->tables[$strTable]->data, $this->tables[$strTable]->fields);
            }
        }

        /**
         * SELECT FROM
         */
        if (preg_match('/^SELECT +([\w ,\*]+) +FROM +(\w+)( +WHERE +([^;]+))*/i', $strSQL, $arrParts)) {
            $strTable = $arrParts[2];

            // If the table has not been instantiated create it
            if (!isset($this->tables[$strTable])) {
                $this->tables[$strTable] = new TextDatabaseTable($strTable, $this->base);
            }

            // If there is no matching table raise an error
            if (!isset($this->tables[$strTable]->fields)) {
                $this->lastError = 1;
                trigger_error(
                    'TextDatabase::query(): no such table: ' . $strTable,
                    E_USER_WARNING
                );
                return false;
            }

            // If we have no data stored, stop now
            if (!isset($this->tables[$strTable]->data)) {
                return new TextDatabaseResult(false, false);
            }

            // If we have a valid field list
            if (preg_match_all('/([\w\'"\*]+) *,/', $arrParts[1] . ",", $arrFields)) {

                // Find the field indexes of the fields to return
                $arrFieldsToReturn = array();
                for ($i = 0; $i < count($arrFields[1]); $i++) {

                    // If one of the fields is a wildcard record all fields and stop looking
                    if ($arrFields[1][$i] == '*') {
                        $arrFieldsToReturn = array_keys($this->tables[$strTable]->fields);
                        break;
                    }

                    // Record the index
                    $intField = array_search($arrFields[1][$i], $this->tables[$strTable]->fields);
                    if ($intField === false) {
                        $this->lastError = 1;
                        trigger_error(
                            'TextDatabase::query(): no such column: ' . $arrFields[1][$i],
                            E_USER_WARNING
                        );
                        return false;
                    };
                    $arrFieldsToReturn[] = $intField;
                }

                // If we have a clause
                if (isset($arrParts[4])) {
                    $arrClause = $this->convertClause($arrParts[4], $strTable);

                    // If clause cannot be converted
                    if ($arrClause === false) {
                        $this->lastError = 1;
                        return false;
                    
                    // Run clause on full data
                    } else {
                        if(!empty($arrClause['preclause'])) eval($arrClause['preclause']);
                        $arrMatched = array();
                        for ($i = 0; $i < count($this->tables[$strTable]->data); $i++) {
                            eval('$blnMatched = ' . $arrClause['clause'] . ';');
                            if ($blnMatched) $arrMatched[] = $this->tables[$strTable]->data[$i];
                        }
                        if (count($arrMatched) == 0) {
                            return new TextDatabaseResult(false, $this->tables[$strTable]->fields);
                        }
                    }

                // We don't have a clause
                } else {
                    $arrMatched = $this->tables[$strTable]->data;
                }

                // Extract fields to return from full table data
                $arrData = array();
                $arrFields = array();
                for ($i = 0; $i < count($arrMatched); $i++) {
                    foreach ($arrFieldsToReturn as $intFieldIndex) {
                        $arrData[$i][] = $arrMatched[$i][$intFieldIndex];
                    }
                }

                // Record field names of fields to return
                for ($i = 0; $i < count($arrFieldsToReturn); $i++) {
                    $arrFields[] = $this->tables[$strTable]->fields[$arrFieldsToReturn[$i]];
                }

                // Return TextDatabaseResult
                return new TextDatabaseResult($arrData, $arrFields);
            }
        }

        /**
         * DELETE FROM
         */
        if (preg_match('/^DELETE +FROM +(\w+)( +WHERE +([^;]+))*/i', $strSQL, $arrParts)) {
            $strTable = $arrParts[1];
            $this->numChanged = 0;

            // If the table has not been instantiated create it
            if (!isset($this->tables[$strTable])) {
                $this->tables[$strTable] = new TextDatabaseTable($strTable, $this->base);
            }

            // If there is no matching table raise an error
            if (!isset($this->tables[$strTable]->fields)) {
                $this->lastError = 1;
                return false;
            }

            // If we have no data stored, stop now
            if (!isset($this->tables[$strTable]->data)) return false;

            // If we have a clause
            if (isset($arrParts[3])) {
                $arrClause = $this->convertClause($arrParts[3], $strTable);
                if(!empty($arrClause['preclause'])) eval($arrClause['preclause']);
                for ($i = 0; $i < count($this->tables[$strTable]->data); $i++) {
                    eval('$blnMatched = ' . $arrClause['clause'] . ';');
                    if ($blnMatched) {
                        $this->tables[$strTable]->removeRow($i);
                        $this->numChanged++;
                    }
                }

                // Return TextDatabaseResult
                return new TextDatabaseResult($this->tables[$strTable]->data, $this->tables[$strTable]->fields);

            // We don't have a clause
            } else {

                // Wipe the whole table, if data exists
                if (isset($this->tables[$strTable]->data)) {
                    $this->numChanged = count($this->tables[$strTable]->data);
                    $this->tables[$strTable]->truncate();
                }

                // Return TextDatabaseResult
                return new TextDatabaseResult(false, $this->tables[$strTable]->fields);
            }
        }

        // Invalid syntax
        $this->lastError = 1;
        trigger_error(
            'TextDatabase::query(): near: "' . $strSQL . '"',
            E_USER_WARNING
        );
        return false;
    }

    /**
     * Execute a query against a given database and returns an array
     * @param string $strSQL The SQL to execute
     * @return array The result set as an array
     */
    public function arrayQuery($strSQL)
    {
        return $this->query($strSQL)->fetchAll();
    }

    /**
     * Returns the number of rows that were changed by the most recent SQL statement
     * @return integer The number of rows
     */
    public function changes()
    {
        return (isset($this->numChanged) ? $this->numChanged : false);
    }

    /**
     * Executes a result-less query against a given database
     * @param string $strSQL The SQL to execute
     */
    public function exec($strSQL)
    {
        $this->query($strSQL);
    }

    /**
     * Returns the error code of the last error for a database
     * @return integer The last error code
     */
    public function lastError()
    {
        return (isset($this->lastError) ? $this->lastError : false);
    }

    /**
     * Returns the textual description of an error code
     * @return integer $intError The error code
     * @return string The error description
     */
    public function errorString($intError)
    {
        global $arrTextDatabaseErrors;
        return (isset($arrTextDatabaseErrors[$intError]) ? $arrTextDatabaseErrors[$intError] : false);

    }

    /**
     * Returns the rowid of the most recently inserted row
     * @return integer The most recently inserted row id
     */
    public function lastInsertRowid()
    {
        return (isset($this->lastRowID) ? $this->lastRowID : false);
    }

    /**
     * Executes a query and returns either an array for one single column or the value of the first row
     * @param string $strSQL The SQL to execute
     * @param boolean $blnFirstRowOnly Whether to return the first row only
     * @return array The result row as an array
     */
    public function singleQuery($strSQL, $blnFirstRowOnly = false)
    {
        $objResponse = $this->query($strSQL);
        if ($objResponse === false) return false;
        if (!isset($objResponse->data)) return false;
        if ($blnFirstRowOnly) {
            return $objResponse->fetch();
        } else {
            $arrData = array();
            for ($i = 0; $i < count($objResponse->data); $i++) {
                $arrData[] = $objResponse->data[$i][0];
            }
            return $arrData;
        }
    }

    /**
     * Execute a query that does not prefetch and buffer all data
     * @return TextDatabaseResult The result
     */
    public function unbufferedQuery($strSQL)
    {
        return $this->query($strSQL);
    }

    /**
     * Removes enclosures and unescapes values
     * @param mixed $varValue The value to parse
     * @return mixed The parsed value
     */
    private static function tidy($varValue)
    {
        if (preg_match('/^[\'"](.*?)[\'"]$/', $varValue, $arrValue)) {
            $varValue = $arrValue[1];
        }
        return preg_replace("/''/", "'", $varValue);
    }

    /**
     * Convert SQL clause to PHP code
     * @param string $strRawClause The SQL clause
     * @param string $strTable The table name
     * @return array Pre-clause and clause PHP code [preclause, clause]
     */
    private function convertClause($strRawClause, $strTable)
    {
        // Extract clauses from sql string
        if (preg_match_all('/(\w+) *(=|IN|LIKE|>|<|>=|<=) *(.+?)( +AND +| +OR +|$)/i', $strRawClause, $arrParts)) {

            // Record separate clause items
            $arrClauses = array();
            for ($i = 0; $i < count($arrParts[0]); $i++) {
                $arrClauses[] = array(
                    'field' => $arrParts[1][$i],
                    'operator' => strtoupper($arrParts[2][$i]),
                    'value' => TextDatabase::tidy($arrParts[3][$i]),
                    'comparison' => trim(strtoupper($arrParts[4][$i])),
                );
            }

            // Initiate our PHP clause code
            $strClause = '';
            $strPreClause = '';

            // Loop through each clause
            foreach($arrClauses as $arrClause) {
                $intField = array_search($arrClause['field'], $this->tables[$strTable]->fields);
                if ($intField === false) {
                    trigger_error(
                        'TextDatabase::query(): no such column: ' . $arrClause['field'],
                        E_USER_WARNING
                    );
                    return false;
                };

                // Convert comparison operators to PHP syntax
                switch ($arrClause['comparison']) {
                    case 'AND':
                        $strComparison = ' && ';
                        break;
                    case 'OR':
                        $strComparison = ' || ';
                        break;
                    default:
                        $strComparison = '';
                }

                // Act depending on clause operator
                switch ($arrClause['operator']) {

                    case '=':
                    case '>':
                    case '<':
                    case '>=':
                    case '<=':
                        if ($arrClause['operator'] == '=') $arrClause['operator'] = '==';
                        $strClause .= '($this->tables[\'' . $strTable . '\']->data[$i][' . $intField . '] ' . $arrClause['operator'] . ' "' . str_replace('"', '\"', $arrClause['value']) . '")' . $strComparison;
                        break;

                    case 'IN':
                        if (preg_match('/\([\d ,]+\)/', $arrClause['value'])) {
                            $strPreClause .= '$a' . $i . ' = array' . $arrClause['value'] . '; ';
                            $strClause .= '(in_array($this->tables[\'' . $strTable . '\']->data[$i][' . $intField . '], $a' . $i . '))' . $strComparison;
                        }
                        break;

                    case 'LIKE':
                        $strValue = str_replace('%', '.*?', str_replace('/', '\/', preg_quote($arrClause['value'])));
                        $strClause .= '(preg_match(\'/^' . str_replace('"', '\"', $strValue) . '$/\', $this->tables[\'' . $strTable . '\']->data[$i][' . $intField . ']))' . $strComparison;
                        break;
                }
            }

            // Return pre-clause and clause PHP code
            return array('preclause' => $strPreClause, 'clause' => $strClause);
        }
        return false;
    }
}


/**
 * TextDatabase result object
 */
class TextDatabaseResult
{
    public $fields;
    public $data;
    private $index;

    /**
     * Constructor
     * @param array $arrData The result data
     * @param array $arrFields The table fields
     */
    public function __construct($arrData, $arrFields)
    {
        if (is_array($arrData)) {
            $this->data = $arrData;
            $this->index = 0;
        }
        if (is_array($arrFields)) $this->fields = $arrFields;
    }

    /**
     * Destructor
     */
    public function __destruct()
    {

    }

    /**
     * Fetches the current row from a result set as an array
     * @return array A row of data
     */
    public function current()
    {
        if (!isset($this->data)) return false;
        if ($this->index >= count($this->data)) return false;
        return $this->extendArray($this->data[$this->index]);
    }

    /**
     * Fetches the next row from a result set as an array
     * @return array A row of data
     */
    public function fetch()
    {
        if (!isset($this->data)) return false;
        if ($this->index >= count($this->data)) return false;
        return $this->extendArray($this->data[$this->index++]);
    }

    /**
     * Fetches all rows from a result set as an array of arrays
     * @return array An array of rows
     */
    public function fetchAll()
    {
        if (!isset($this->data)) return array();
        $arrData = array();
        for ($i = 0; $i < count($this->data); $i++) {
            $arrData[] = $this->extendArray($this->data[$i]);
        }
        return $arrData;
    }

    /**
     * Returns the number of rows in a buffered result set
     * @return integer The number of rows
     */
    public function numRows()
    {
        if (!isset($this->data)) return false;
        return count($this->data);
    }

    /**
     * Fetches a column from the current row of a result set
     * @param mixed The column index or name
     * @return mixed The column value
     */
    public function column($varColumn)
    {
        if (!isset($this->data)) return false;
        if (!$this->valid()) return false;
        if (is_integer($varColumn)) {
            return $this->data[$this->index][$varColumn];
        } else {
            $intField = array_search($varColumn, $this->fields);
            if ($intField === false) return false;
            return $this->data[$this->index][$intField];
        }
    }

    /**
     * Fetches the first column of a result set as a string
     * @return string The field value
     */
    public function fetchSingle()
    {
        if (!isset($this->data)) return false;
        return strval($this->data[0][0]);
    }

    /**
     * Fetches the first column of a result set as a string
     * @return string The field value
     */
    public function fetchString()
    {
        return $this->fetchSingle();
    }

    /**
     * Returns the name of a particular field
     * @return string The field name
     */
    public function fieldName($intFieldIndex)
    {
        if (!isset($this->fields[$intFieldIndex])) return false;
        return $this->fields[$intFieldIndex];
    }

    /**
     * Returns whether or not more rows are available
     * @return boolean Whether or not more rows are available
     */
    public function hasMore()
    {
        if (!isset($this->index)) return false;
        return ($this->index < count($this->data) - 1);
    }

    /**
     * Returns whether or not a previous row is available
     * @return boolean Whether or not a previous row is available
     */
    public function hasPrev()
    {
        if (!isset($this->index)) return false;
        return ($this->index > 0);
    }

    /**
     * Returns the current row index
     * @return integer The current row index
     */
    public function key()
    {
        if (!isset($this->index)) return false;
        return $this->index;
    }

    /**
     * Seek to the next row number
     * @return boolean The success of the operation
     */
    public function next()
    {
        if (!isset($this->data)) return false;
        if ($this->index >= count($this->data)) {
            return false;
        } else {
            $this->index++;
            return true;
        }
    }

    /**
     * Returns the number of fields in a result set
     * @return integer The number of fields
     */
    public function numFields()
    {
        if (!isset($this->fields)) return false;
        return count($this->fields);
    }

    /**
     * Seek to the previous row number of a result set
     * @return boolean The success of the operation
     */
    public function prev()
    {
        if (!isset($this->data)) return false;
        if ($this->index == 0) {
            return false;
        } else {
            $this->index--;
            return true;
        }
    }

    /**
     * Seek to the first row number
     * @return boolean The success of the operation
     */
    public function rewind()
    {
        if (!isset($this->data)) return false;
        $this->index = 0;
        return true;
    }

    /**
     * Seek to a particular row number of a buffered result set
     * @param integer $intRow The row to seek to
     * @return boolean The success of the operation
     */
    public function seek($intRow)
    {
        if (!isset($this->data)) return false;
        if ($intRow < count($this->data) - 1) {
            $this->index = $intRow;
            return true;
        } else {
            return false;
        }
    }

    /**
     * Returns whether more rows are available
     * @return boolean Whether more rows are available
     */
    public function valid()
    {
        return ($this->index < count($this->data) - 1);
    }

    /**
     * Extends an array, setting both numeric and string (field name) keys for a value
     * @param array $arrData The data row to expand
     * @return array The extended array
     */
    private function extendArray($arrData)
    {
        $arrArray = array();
        for ($i = 0; $i < count($arrData); $i++) {
            $arrArray[$i] = $arrData[$i];
            $arrArray[$this->fields[$i]] = $arrData[$i];
        }
        return $arrArray;
    }
}


/**
 * TextDatabase table object
 */
class TextDatabaseTable
{
    public $name;
    public $file;
    public $fields;
    public $data;
    public $primaryKeyIndex;
    private $changed = false;

    /**
     * Constructor
     * @param string $strTable The table name
     * @param string $strBase The base file name
     */
    public function __construct($strTable, $strBase)
    {
        $this->name = $strTable;
        $this->file = $strBase . '.' . $strTable . '.csv';
        $this->load();
    }

    /**
     * Destructor
     */
    public function __destruct()
    {
        if ($this->changed) $this->save();
    }

    /**
     * Loads a table from file
     */
    public function load()
    {
        if (file_exists($this->file)) {
            $this->data = array();
            $objHandle = fopen($this->file, "r");

            // Load rows
            while (($arrData = fgetcsv($objHandle)) !== FALSE) {
                if (is_array($arrData) && !empty($arrData[0])) {
                    if (is_array($this->fields)) {
                        $this->addRow($arrData);
                    } else {
                        $this->fields = $arrData;
                    }
                }
            }
            fclose($objHandle);

            // Identify primary key
            for ($i = 0; $i < count($this->fields); $i++) {
                if (preg_match('/(\w+){PK}/', $this->fields[$i], $arrParts)) {
                    $this->fields[$i] = $arrParts[1];
                    $this->primaryKeyIndex = $i;
                }
            }
        }
        $this->changed = false;
    }

    /**
     * Saves a table to file
     */
    public function save()
    {
        $arrFields = $this->fields;
        if (isset($this->primaryKeyIndex)) {
            $arrFields[$this->primaryKeyIndex] = $arrFields[$this->primaryKeyIndex] . '{PK}';
        }
        $objHandle = fopen($this->file, 'w');
        fputcsv($objHandle, $arrFields);
        if (isset($this->data)) {
            foreach ($this->data as $arrRecord) {
                fputcsv($objHandle, $arrRecord);
            }
        }
        fclose($objHandle);
        @chmod($this->file, 0660);
        $this->changed = false;
    }

    /**
     * Adds a row of data to the table
     * @param array $arrRow An array of data
     * @return integer The row id of the record inserted (if available)
     */
    public function addRow($arrRow)
    {
        if (!isset($this->data)) $this->data = array();
        $arrKeys = array_keys($arrRow);
        $arrValues = array_fill(0, count($this->fields), '');

        // If we have no string (field name) keys for the array
        if ($arrKeys[0] === 0) {
            for ($i = 0; $i < count($arrRow); $i++) {
                $arrValues[$i] = $arrRow[$i];
            }

        // We have string (field name) keys
        } else {

            // Get the autogenerated primary key (may be overridden in next code block)
            if (isset($this->primaryKeyIndex)) {
                $arrValues[$this->primaryKeyIndex] = $this->getNextKey();
            }

            // Create data array
            foreach($arrRow as $strField => $varValue) {
                $intField = array_search($strField, $this->fields);
                if ($intField === false) continue;
                $arrValues[$intField] = $varValue;
            }
        }
        $this->data[] = $arrValues;
        $this->changed = true;

        // If we have a primary key return the id
        if (isset($this->primaryKeyIndex)) return $arrValues[$this->primaryKeyIndex];
    }

    /**
     * Removes a row of data
     * @param integer $intIndex The array index to remove
     */
    public function removeRow($intIndex)
    {
        unset($this->data[$intIndex]);
        $this->data = array_merge($this->data);
        $this->changed = true;
    }

    /**
     * Returns the next primary key value for the table
     * @return integer The primary key value
     */
    private function getNextKey()
    {
        if (!isset($this->data)) return 1;
        $intMax = 0;
        for ($i =0; $i < count($this->data); $i++) {
            if ($this->data[$i][$this->primaryKeyIndex] > $intMax) $intMax = $this->data[$i][$this->primaryKeyIndex];
        }
        return ++$intMax;
    }

    /**
     * Clears all data from the table
     */
    public function truncate()
    {
        if (isset($this->data)) $this->changed = true;
        unset($this->data);
    }
}

/**
 * Array of error descriptions
 */
$arrTextDatabaseErrors = array(
    1 => 'SQL logic error or missing database',
);
?>
